Entity Relationship Diagram (ERD) — Database Design

AquaX — Shrimp Farming Management Platform

Document Info
Version 1.1
Status Draft — Database Baseline + Current Prisma Alignment
Created Date 2026-09-16
Last Updated 2026-09-17
Owner Backend / Architecture
Reviewers Product, QA, Security, Operations
Source Of Truth backend/prisma/schema.prisma
Related Docs 03_SRS — Software Requirements Specification.md, 01-product/requirements/data-requirements.md, 04-architecture/database-design.md

Implementation alignment note: This ERD is derived from the current Prisma schema. Logical requirements that do not yet have full physical implementation are marked PARTIAL, PLANNED or TBD.

Table of Contents

  1. Database Overview
  2. Naming And Schema Conventions
  3. Domain Model Inventory
  4. Core ERD
  5. Operations ERD
  6. IoT And Telemetry ERD
  7. Alerts, Tickets And Notifications ERD
  8. Handbook, AI And Reports ERD
  9. Location ERD
  10. Key Constraints And Indexes
  11. Data Requirement Mapping
  12. Data Retention
  13. Migration And Change Policy
  14. Open Data Design Gaps
  15. Traceability
  16. Document History

1. Database Overview

AquaX uses PostgreSQL through Prisma. Local infrastructure uses TimescaleDB/PostgreSQL images for telemetry-oriented workloads, while the Prisma schema is the current source of truth for application tables and relations.

The database supports these major domains:

Domain Purpose Status
Identity and access Users, sessions, farm memberships, pond assignments and audit logs. PARTIAL
Farm structure Farms, ponds, crops, crop catalogs and location references. PARTIAL
Farming operations Manual water records, minerals, siphon, productivity and feeding records. PARTIAL
IoT and telemetry Sensors, readings, thresholds, devices, mappings, registrations, command logs and automation rules. PARTIAL
Alerts and incidents Alerts, alert history, pond issue reports, tickets, ticket attachments, comments and SLA-related state. PARTIAL
Knowledge Handbook articles, approvals, versions and bookmarks. PARTIAL
Notifications In-app/push/email notification records, configs and device tokens. PARTIAL
AI and reports AI prediction logs and report history. PARTIAL / PLANNED
Location Province and ward administrative references. CONFIRMED

2. Naming And Schema Conventions

Convention Current Practice
Primary keys String @id @default(uuid()) for most application models.
Timestamps Most operational models include createdAt; mutable models usually include updatedAt.
Table names Prisma models map to snake_case table names through @@map.
Enums Prisma enums define roles, statuses, sensor types, device modes, ticket statuses and AI prediction categories.
Soft delete Some models use deletedAt or isDeleted, for example Pond and Ticket.
Audit preservation Several nullable user relations use onDelete: SetNull to preserve history.
Cascades Child records often cascade from parent operational context such as farm, pond, ticket or article.

3. Domain Model Inventory

3.1 Identity And Access

Model Table Purpose
User users Account, role array, password/session metadata and relation hub.
Session user_sessions Refresh token/session tracking across devices.
FarmMember farm_members Many-to-many farm membership with role.
PondAssignment pond_assignments One technician assignment per pond.
AuditLog audit_logs Generic audit/action history.

3.2 Farm, Pond And Crop

Model Table Purpose
Farm farms Farm profile and owner relation.
Pond ponds Pond profile, farm relation and core operational context.
Crop crops Crop/farming cycle data per pond.
CropSpecies crop_species Species catalog.
CropSizeRange crop_size_ranges Shrimp size range catalog.
TodoTask todo_tasks User-owned farm/pond tasks.

3.3 Farming Operations

Model Table Purpose
ManualWaterRecord manual_water_records Manual pH, alkalinity, temperature, salinity, DO and ORP records.
MineralRecord mineral_records Mineral application logs.
SiphonRecord siphon_records Siphon quantity and bottom condition logs.
ProductivityRecord productivity_records Productivity/harvest-related records.
FeedingRecord feeding_records Per-session feeding data and AI suggestion snapshot fields.
FeedType feed_types Feed type catalog.
FarmingLogAttachment farming_log_attachments Attachments for farming log records using polymorphic recordId and recordType.

3.4 IoT, Device And Sensor

Model Table Purpose
Sensor sensors Pond sensor metadata and state.
SensorReading sensor_readings Sensor time-series readings.
ParameterThreshold parameter_thresholds Pond/global metric thresholds.
Device devices IoT controllable device metadata and state.
DeviceCommandLog device_command_logs Device command audit and execution result.
AutoRule auto_rules Automatic rule configuration for pond/device actions.
IotDeviceMapping iot_device_mappings Vendor external ID to internal device mapping.
IotDeviceRegistration iot_device_registrations Discovered external IoT devices awaiting mapping.

3.5 Alerts, Tickets And Support

Model Table Purpose
Alert alerts Water/device/system alert.
AlertStatusHistory alert_status_history Alert lifecycle history.
PondIssueReport pond_issue_reports Older/simple pond issue report workflow.
Ticket tickets Technical incident/support workflow.
TicketAttachment ticket_attachments Ticket media/files.
TicketStatusHistory ticket_status_history Ticket lifecycle history.
TicketWaterParameter ticket_water_parameters Water metric snapshot attached to ticket.
TicketComment ticket_comments Ticket discussion/progress comments.

3.6 Handbook, Notifications, AI And Reports

Model Table Purpose
HandbookArticle handbook_articles Farming handbook article.
ArticleStatusHistory article_status_history Handbook article workflow history.
HandbookVersion handbook_versions Article version snapshots.
ArticleBookmark article_bookmarks User favorite/bookmark relation.
Notification notifications In-app/push/email notification record.
NotificationConfig notification_configs Notification rules and recipients.
NotificationDeviceToken notification_device_tokens Push device tokens.
IncidentResponseSetting incident_response_settings SLA, assignment and overdue settings.
AIPredictionLog ai_prediction_logs AI prediction/recommendation activity log.
ReportHistory report_history Generated/exported report metadata.

3.7 Location

Model Table Purpose
Province provinces Vietnam province reference data.
Ward wards Ward reference data; farms may reference ward.

4. Core ERD

erDiagram
  USER ||--o{ SESSION : has
  USER ||--o{ AUDIT_LOG : writes
  USER ||--o{ FARM : owns
  USER ||--o{ FARM_MEMBER : joins
  USER ||--o{ POND_ASSIGNMENT : assigned
  USER ||--o{ POND_ASSIGNMENT : assigns
  USER ||--o{ TODO_TASK : owns

  FARM ||--o{ POND : contains
  FARM ||--o{ FARM_MEMBER : has
  FARM ||--o{ TODO_TASK : scopes
  WARD ||--o{ FARM : locates
  PROVINCE ||--o{ WARD : contains

  POND ||--o{ CROP : has
  POND ||--o{ POND_ASSIGNMENT : has
  POND ||--o{ TODO_TASK : scopes
  USER ||--o{ CROP : records

4.1 Core Relationship Notes

Relationship Schema Detail
Farm.ownerId -> User.id Optional owner; onDelete: SetNull.
Pond.farmId -> Farm.id Required farm; onDelete: Cascade.
PondAssignment.pondId Unique, enforcing one technician assignment per pond in current schema.
FarmMember.userId + farmId Unique pair, supporting many-to-many farm membership.
Farm.wardId -> Ward.id Optional administrative location relation.

5. Operations ERD

erDiagram
  POND ||--o{ CROP : has
  POND ||--o{ MANUAL_WATER_RECORD : records
  POND ||--o{ MINERAL_RECORD : records
  POND ||--o{ SIPHON_RECORD : records
  POND ||--o{ PRODUCTIVITY_RECORD : records
  POND ||--o{ FEEDING_RECORD : records

  CROP ||--o{ MANUAL_WATER_RECORD : groups
  CROP ||--o{ MINERAL_RECORD : groups
  CROP ||--o{ SIPHON_RECORD : groups
  CROP ||--o{ PRODUCTIVITY_RECORD : groups
  CROP ||--o{ FEEDING_RECORD : groups

  USER ||--o{ MANUAL_WATER_RECORD : records
  USER ||--o{ MINERAL_RECORD : records
  USER ||--o{ SIPHON_RECORD : records
  USER ||--o{ PRODUCTIVITY_RECORD : records
  USER ||--o{ FEEDING_RECORD : records
  USER ||--o{ FARMING_LOG_ATTACHMENT : uploads

5.1 Operations Constraints

Model Important Constraints / Indexes
ManualWaterRecord Unique [pondId, date]; indexed by pond, crop and recordedBy.
SiphonRecord Unique [pondId, date]; indexed by pond and crop.
ProductivityRecord Unique [pondId, date, method]; indexed by pond and crop.
FeedingRecord Indexed by pond, crop and [pondId, date].
FarmingLogAttachment Polymorphic attachment via recordId and recordType; no FK to source record.

6. IoT And Telemetry ERD

erDiagram
  POND ||--o{ SENSOR : has
  SENSOR ||--o{ SENSOR_READING : records
  POND ||--o{ PARAMETER_THRESHOLD : configures

  POND ||--o{ DEVICE : has
  DEVICE ||--o{ DEVICE_COMMAND_LOG : logs
  DEVICE ||--o{ AUTO_RULE : targeted_by
  POND ||--o{ AUTO_RULE : owns
  USER ||--o{ DEVICE_COMMAND_LOG : requests
  USER ||--o{ AUTO_RULE : creates

  DEVICE ||--o{ IOT_DEVICE_MAPPING : maps
  DEVICE ||--o{ IOT_DEVICE_REGISTRATION : registered_as

6.1 Telemetry And Device Notes

Model Notes
SensorReading Time-series style table indexed by [sensorId, timestamp].
ParameterThreshold Unique [pondId, parameter]; pondId is optional. Because PostgreSQL treats NULL values as distinct in unique indexes, this may not enforce a single global/default row per parameter without application logic or a partial index.
Device Tracks status, mode, connectionStatus, command timeout and recent command/response timestamps.
DeviceCommandLog Preserves command lifecycle with ExecutionStatus: pending, success, failed, timeout.
IotDeviceMapping Unique vendor/external device pair.
IotDeviceRegistration Stores discovered external devices and last payload/topic metadata.

7. Alerts, Tickets And Notifications ERD

erDiagram
  POND ||--o{ ALERT : has
  USER ||--o{ ALERT : assigned
  ALERT ||--o{ ALERT_STATUS_HISTORY : tracks
  USER ||--o{ ALERT_STATUS_HISTORY : changes

  POND ||--o{ POND_ISSUE_REPORT : has
  USER ||--o{ POND_ISSUE_REPORT : creates
  USER ||--o{ POND_ISSUE_REPORT : assigned_to

  POND ||--o{ TICKET : has
  DEVICE ||--o{ TICKET : referenced_by
  ALERT ||--o{ TICKET : source_for
  USER ||--o{ TICKET : creates
  USER ||--o{ TICKET : assigned_to
  USER ||--o{ TICKET : assigns
  TICKET ||--o{ TICKET_ATTACHMENT : has
  TICKET ||--o{ TICKET_STATUS_HISTORY : tracks
  TICKET ||--o{ TICKET_WATER_PARAMETER : snapshots
  TICKET ||--o{ TICKET_COMMENT : has

  USER ||--o{ NOTIFICATION : receives
  USER ||--o{ NOTIFICATION_CONFIG : creates
  USER ||--o{ NOTIFICATION_DEVICE_TOKEN : registers
  USER ||--o{ INCIDENT_RESPONSE_SETTING : updates

7.1 Alert And Ticket Notes

Model Notes
Alert Optional pond and assignee; stores water threshold fields and lifecycle timestamps.
AlertStatusHistory Cascade from alert; changedBy user is set null on delete.
PondIssueReport Simple pond issue workflow with unique [pondId, code]; creator and assignee are optional user relations.
Ticket Optional pond/device/source alert; soft delete via isDeleted, deletedAt, deletedById.
TicketAttachment Cascade from ticket; uploader relation required.
TicketStatusHistory Tracks status transitions.
Notification Polymorphic target through targetId and targetType; no FK to target object.
NotificationConfig Unique [eventType, channel].

8. Handbook, AI And Reports ERD

erDiagram
  USER ||--o{ HANDBOOK_ARTICLE : creates
  USER ||--o{ HANDBOOK_ARTICLE : approves
  HANDBOOK_ARTICLE ||--o{ ARTICLE_STATUS_HISTORY : tracks
  HANDBOOK_ARTICLE ||--o{ HANDBOOK_VERSION : versions
  HANDBOOK_ARTICLE ||--o{ ARTICLE_BOOKMARK : bookmarked_by
  USER ||--o{ ARTICLE_STATUS_HISTORY : changes
  USER ||--o{ HANDBOOK_VERSION : creates
  USER ||--o{ ARTICLE_BOOKMARK : owns

  POND ||--o{ AI_PREDICTION_LOG : has
  REPORT_HISTORY {
    string farmId
    string farmName
    string pondId
    string pondName
    string downloadUrl
  }

8.1 Knowledge, AI And Report Notes

Model Notes
HandbookArticle Article workflow: draft, pending approval, approved, archived.
HandbookVersion Unique [articleId, versionNumber].
ArticleBookmark Unique [userId, articleId].
AIPredictionLog Physical model exists for AI prediction/activity logs; production prediction engine is not implemented.
ReportHistory Stores farmId/pondId as nullable strings without Prisma FK relations; the Mermaid block intentionally does not draw FK edges.

9. Location ERD

erDiagram
  PROVINCE ||--o{ WARD : contains
  WARD ||--o{ FARM : locates

10. Key Constraints And Indexes

10.1 Uniqueness

Model Constraint
User email unique.
Farm [ownerId, code] unique.
Pond [farmId, code], [farmId, name] unique.
PondAssignment [pondId] unique.
FarmMember [userId, farmId] unique.
ManualWaterRecord [pondId, date] unique.
SiphonRecord [pondId, date] unique.
ProductivityRecord [pondId, date, method] unique.
Sensor [pondId, name] unique.
ParameterThreshold [pondId, parameter] unique.
Device code unique.
IotDeviceMapping [vendor, externalDeviceId] unique.
IotDeviceRegistration [vendor, externalDeviceId] unique.
Alert code unique.
PondIssueReport [pondId, code] unique.
Ticket code unique.
HandbookVersion [articleId, versionNumber] unique.
ArticleBookmark [userId, articleId] unique.
Notification fingerprint unique.
NotificationConfig [eventType, channel] unique.
NotificationDeviceToken [userId, token] unique.
ReportHistory code unique.

10.2 High-Value Indexes

Domain Indexes
Auth/session User.email, Session.userId/isActive, Session.refreshTokenHash.
Farm/pond Farm.ownerId, Farm.status, Pond.farmId, Pond.status, Pond.deletedAt.
Operations Pond/crop/date indexes across records; feeding [pondId, date].
Telemetry SensorReading.sensorId/timestamp, Sensor.pondId/type.
Alerts/tickets Alert status/severity/type/pond/date indexes; ticket status/SLA/severity/pond/date indexes.
Notifications Notification.userId/readAt, Notification.userId/eventType, Notification.createdAt.
AI/reporting AIPredictionLog.pondId, predictedAt, type; ReportHistory.createdAt, type, farmId, pondId.

11. Data Requirement Mapping

SRS Data ID Logical Entity Physical Model(s) Status
DATA-01 User User, Session, FarmMember, PondAssignment PARTIAL
DATA-02 Farm Farm, Province, Ward PARTIAL
DATA-03 Pond Pond, PondAssignment PARTIAL
DATA-04 Crop Crop, CropSpecies, CropSizeRange PARTIAL
DATA-05 Sensor Sensor PARTIAL
DATA-06 Water Quality Record SensorReading, ManualWaterRecord PARTIAL
DATA-07 Device Device, IotDeviceMapping, IotDeviceRegistration PARTIAL
DATA-08 Device Command Log DeviceCommandLog PARTIAL
DATA-09 Feeding Record FeedingRecord, FeedType PARTIAL
DATA-10 Mineral Record MineralRecord PARTIAL
DATA-11 Siphon Record SiphonRecord PARTIAL
DATA-12 Productivity Record ProductivityRecord PARTIAL
DATA-13 Alert Alert, AlertStatusHistory PARTIAL
DATA-14 Ticket Ticket, TicketStatusHistory, TicketComment, TicketWaterParameter PARTIAL
DATA-15 Ticket Attachment TicketAttachment PARTIAL
DATA-16 Handbook Article HandbookArticle, ArticleStatusHistory, HandbookVersion, ArticleBookmark PARTIAL
DATA-17 Chatbot Conversation No dedicated conversation model; AIPredictionLog covers AI prediction/activity logging only. PLANNED
DATA-18 Notification Notification, NotificationConfig, NotificationDeviceToken PARTIAL
DATA-19 Configuration ParameterThreshold, AutoRule, NotificationConfig, IncidentResponseSetting, catalogs PARTIAL

12. Data Retention

Retention requirements are not fully approved. Baseline expectations from SRS:

Data Type Minimum Retention Current Status
Sensor data 1 year; extension by customer need. TBD
Device history and command logs 1 year. TBD
Feeding, manual environment, mineral and siphon data Full crop history across multiple crops. PARTIAL
Alerts, tickets and audit logs At least 1 year. TBD
Handbook content Permanent by version; no hard delete after AI reference. PARTIAL
Chatbot conversations and images At least crop lifecycle; privacy/delete policy TBD. PLANNED
Report files/download URLs Retention period TBD. TBD

13. Migration And Change Policy

  • backend/prisma/schema.prisma is the physical schema source of truth.
  • Use Prisma migrations for schema changes.
  • Production deploys must use prisma migrate deploy.
  • Avoid ad hoc db push for production.
  • Schema changes affecting role names, enum values, ticket/alert states, telemetry records or FK behavior require review.
  • Data migrations must include rollback or compensating strategy when destructive changes are possible.
  • Update this ERD after every schema migration that changes tables, relations, enum values, indexes or retention behavior.

14. Open Data Design Gaps

Gap Impact Owner
Published generated ERD from Prisma is not automated. ERD can drift from schema. Backend
Chatbot conversation storage is not physically modeled. SRS DATA-17 remains planned. Product/AI/Backend
ReportHistory uses string farm/pond references instead of FK relations. Historical reports preserve names but relational integrity is limited. Backend/Product
FarmingLogAttachment uses polymorphic record reference without FK. Attachments need application-level integrity checks. Backend
Notification target is polymorphic without FK. Target integrity is application-level. Backend
Retention/archival policy is not approved. Telemetry, audit, files and AI history lifecycle remains unclear. Security/Ops
Offline sync conflict strategy is not defined. Mobile offline writes cannot be safely accepted. Mobile/Backend
Timescale hypertable/retention automation is not captured in Prisma schema. Telemetry scaling policy remains external/TBD. Backend/DevOps

15. Traceability

Area Reference
Product requirements 01_PRD — Product Requirements Document.md
Business requirements 02_BRD — Business Requirements Document.md
Software requirements 03_SRS — Software Requirements Specification.md
Split data requirements 01-product/requirements/data-requirements.md
Architecture database design 04-architecture/database-design.md
Prisma schema backend/prisma/schema.prisma
Telemetry architecture current/architecture/aquax-timescaledb-telemetry.md
IoT ingestion architecture current/architecture/iot-mqtt-ingestion-architecture.md
Gaps DOCUMENTATION-GAPS.md

16. Document History

Version Date Author Changes
1.0 2026-09-16 Backend / Architecture Created ERD and database design from current Prisma schema and data requirements.
1.1 2026-09-17 Backend / Architecture Corrected Prisma alignment notes for PondIssueReport, ReportHistory non-FK references and nullable ParameterThreshold uniqueness behavior.

End of Entity Relationship Diagram / Database Design